package com.cms.biz.pending.query;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.builder.base.data.ESPRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;
import com.pub.util.StringFunction;


/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPPendingFlowQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";
	
	protected     String                  msTableName1 = "";
	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
	
			// 获取查询数据
			queryDataset = getFlowDataSet(stmt, PO);
			// 封装返回数据
		   	RO = new JResponseObject(convertEFDataSet2Json(queryDataset), 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 获取流程数据
	 * @throws Exception
	 */
	public EFDataSet getFlowDataSet(JStatement stmt, JParamObject PO) throws Exception {
		String              strSql = "";
		String              USER_ID = PO.GetValueByParamName("USER_ID", "");
		String           USER_ORGID = PO.GetValueByParamName("USER_ORGID", "");
		EFDataSet       flowDataSet = EFDataSet.getInstance();
		ResultSet                rs = null;
		int           flowTaskCount = 0;
		
		//查询有权限的流程
		strSql = "select FLOW_BH as FLOW_ID, FLOW_MC, '' as OP_ID, '' as NODE_ID, '' as BIZ_GUID, '' as BIZ_DJBH, '' as BIZ_KJQJ, '' as OP_NOTE, " 
			   + "'' as OP_USER_NAME, '' as F_TJRQ from FLOW_STATUS_LIST where FLOW_STATUS = '1' and exists (select 1 from FLOW_USER_LIST where " 
			   + " FLOW_STATUS_LIST.FLOW_BH = FLOW_USER_LIST.FLOW_BH and ((FLOW_USER_LIST.FLOW_BM = '" + USER_ORGID 
			   + "' and FLOW_USER_LIST.F_TYPE = 'BM') or (FLOW_USER_LIST.FLOW_USER = '" + USER_ID 
			   + "' and FLOW_USER_LIST.FLOW_BM = '" + USER_ORGID + "' and FLOW_USER_LIST.F_TYPE = 'USER')))"
			   + " order by FLOW_GUID";
		rs = stmt.executeQuery(strSql);
		flowDataSet = DataSetUtils.resultSet2DataSet(rs, flowDataSet);
		getFlowNodeDataSet(stmt, PO, flowDataSet);
		for(int i = flowDataSet.getRowCount() - 1; i >= 0; i--) {
			flowTaskCount = flowDataSet.getRowSet(i).getNumber("FLOW_TASK_COUNT", 0.0).intValue();
			if(flowTaskCount == 0) flowDataSet.removeRowSet(i);
		}
		return flowDataSet;
	}
	
	/**
	 * 根据流程ID获取流程节点数据
	 * @throws Exception
	 */
	public void getFlowNodeDataSet(JStatement stmt, JParamObject PO, EFDataSet flowDataSet) throws Exception {
		String               strSql = "";
		String              USER_ID = PO.GetValueByParamName("USER_ID", "");
		String           USER_ORGID = PO.GetValueByParamName("USER_ORGID", "");
		int                   count = 0;
		EFDataSet   flowNodeDataSet = EFDataSet.getInstance();
		ResultSet                rs = null;
		String               FmtStr = "00";
		java.text.DecimalFormat  df = new java.text.DecimalFormat(FmtStr);
		
		for(int i = 0; i < flowDataSet.getRowCount(); i++) {
			flowNodeDataSet = EFDataSet.getInstance();
			//取出流程下有权限的节点
			strSql = "select FLOW_ID, NODE_NAME as FLOW_MC, NODE_ID, '' as BIZ_GUID, " 
			       + " '' as OP_ID, '' as BIZ_DJBH, '' as BIZ_KJQJ, '' as OP_NOTE, '' as OP_USER_NAME, '' as F_TJRQ" 
			       + " from FLOW_NODE_LIST where FLOW_ID = '" + flowDataSet.getRowSet(i).getString("FLOW_ID", "") + "' and " 
				   + " exists(SELECT 1 FROM FLOW_USER_LIST where FLOW_USER_LIST.FLOW_BH = FLOW_NODE_LIST.FLOW_ID and ((FLOW_USER_LIST.FLOW_BM = '"
				   + USER_ORGID
				   + "' and FLOW_USER_LIST.F_TYPE = 'BM')or (FLOW_USER_LIST.FLOW_USER = '"
				   + USER_ID
				   + "' and FLOW_USER_LIST.FLOW_BM = '" + USER_ORGID + "' and FLOW_USER_LIST.F_TYPE = 'USER'))"
		       	   + " and FLOW_USER_LIST.NODE_BH = FLOW_NODE_LIST.NODE_ID)"
				   + " order by NODE_ID";
			rs = stmt.executeQuery(strSql);
			flowNodeDataSet = DataSetUtils.resultSet2DataSet(rs, flowNodeDataSet);
			flowDataSet.getRowSet(i).putString("id", df.format(i + 1));
			flowDataSet.getRowSet(i).putString("pid", "");
			
			//计算节点下共有多少任务
			count = getFlowTaskDataSet(stmt, PO, flowNodeDataSet, df.format(i + 1), flowDataSet.getRowSet(i).getString("FLOW_ID", ""), flowDataSet.getRowSet(i).getString("FLOW_MC", ""));
			
			if(count > 0) {
				for(int j = flowNodeDataSet.getRowCount() - 1; j >= 0; j--) {
					if(flowNodeDataSet.getRowSet(j).getDataSet("FLOW_TASK_LIST") == null || flowNodeDataSet.getRowSet(j).getDataSet("FLOW_TASK_LIST").getRowCount() == 0) {
						flowNodeDataSet.removeRowSet(j);
					}
				}
				flowDataSet.getRowSet(i).putString("FLOW_MC", flowDataSet.getRowSet(i).getString("FLOW_MC", "") + "[共有任务" + count + "个]");
				flowDataSet.getRowSet(i).setDataSet("FLOW_NODE_LIST", flowNodeDataSet);
				flowDataSet.getRowSet(i).putNumber("FLOW_TASK_COUNT", count);
			}
		}
	}
	
	/**
	 * 根据流程节点ID获取流程数据
	 * @throws Exception
	 */
	public int getFlowTaskDataSet(JStatement stmt, JParamObject PO, EFDataSet flowNodeDataSet, String PID, String FLOW_BH, String FLOW_MC) throws Exception {
		String                     strSql = "";
		String               strSqlColumn = "";
		String                    USER_ID = PO.GetValueByParamName("USER_ID", "");
		String                 USER_ORGID = PO.GetValueByParamName("USER_ORGID", "");
		EFDataSet         flowTaskDataSet = EFDataSet.getInstance();
		ResultSet                      rs = null;
		EFRowSet                 rsRowSet = null;
		int                         count = 0;
		String                     FmtStr = "00";
		java.text.DecimalFormat        df = new java.text.DecimalFormat(FmtStr);

//		strSqlColumn = " select BIZ_DATE,BIZ_DJBH,BIZ_GUID,FLOW_ID, from_unixtime(substring(OP_TIME, 1, 10)) as F_TJRQ, "
//			         + " F_CHAR01,F_CHAR02,F_CHAR03,F_CHAR04,F_CHAR05,F_CHAR06,F_CHAR07,F_CHAR08,F_CHAR09,F_CHAR10," 
//			         + " F_CHAR11,F_CHAR12,F_CHAR13,F_CHAR14,F_CHAR15,F_CHAR16,F_CHAR17,F_CHAR18,F_CHAR19,F_CHAR20,"
//			         + " F_DATE01,F_DATE02,F_DATE03,F_DATE04,F_DATE05,F_DATE06,F_DATE07,F_DATE08,F_DATE09,F_DATE10," 
//			         + " F_DATE11,F_DATE12,F_DATE13,F_DATE14,F_DATE15,F_DATE16,F_DATE17,F_DATE18,F_DATE19,F_DATE20,"
//			         + " F_NUM01,F_NUM02,F_NUM03,F_NUM04,F_NUM05,F_NUM06,F_NUM07,F_NUM08,F_NUM09,F_NUM10," 
//			         + " F_NUM11,F_NUM12,F_NUM13,F_NUM14,F_NUM15,F_NUM16,F_NUM17,F_NUM18,F_NUM19,F_NUM20,"
//			         + " NODE_SRC,NODE_SRC_NAME,NODE_TAG,NODE_TAG_NAME,OP_BMBH,OP_ID,OP_NODE,OP_NOTE,OP_TIME,OP_USER,OP_USER_NAME,"
//			         + " POP_ID,RESR_IN_CAUSE,RESR_OUT_CAUSE,RESR_STATUS,TASK_STATUS,TASK_TO_UNIT,TASK_TO_UNIT_NAME,"
//			         + " TASK_TO_USER,TASK_TO_USER_NAME";

		strSqlColumn = " select BIZ_DATE as BIZ_KJQJ, BIZ_DJBH,FLOW_ID, BIZ_GUID, ' ' as FLOW_MC, OP_ID, from_unixtime(substring(OP_TIME, 1, 10)) as F_TJRQ, "
	        		 + " OP_NOTE, OP_USER_NAME";
		for(int i = 0; i < flowNodeDataSet.getRowCount(); i++) {
			flowTaskDataSet = EFDataSet.getInstance();
			//取出流程下有权限的节点
			strSql = strSqlColumn + ", '" + flowNodeDataSet.getRowSet(i).getString("NODE_ID", "") + "' as NODE_ID from FLOW_TASK_LIST where FLOW_ID = '" + flowNodeDataSet.getRowSet(i).getString("FLOW_ID", "") + "' and " 
				   + " TASK_STATUS = 'processing' and RESR_STATUS = 'pending' and RESR_IN_CAUSE <> 'waiting' and NODE_TAG = '" + flowNodeDataSet.getRowSet(i).getString("NODE_ID", "") + "' and " 
				   + "(TASK_TO_UNIT IS NULL or TASK_TO_UNIT = '' or TASK_TO_UNIT = ' ' or TASK_TO_UNIT = '" + USER_ORGID + "' or (FLOW_ID = '" + FLOW_BH + "' and NODE_TAG = 'N1')) and "
				   + "(TASK_TO_USER IS NULL or TASK_TO_USER = '' or TASK_TO_USER = ' ' or TASK_TO_USER = '" + USER_ID + "' or (FLOW_ID = '" + FLOW_BH + "' and NODE_TAG = 'N1')) "
				   + " order by BIZ_KJQJ, BIZ_DJBH";
			rs = stmt.executeQuery(strSql);

			while(rs != null && rs.next()) {
				rsRowSet = resultSet2RowSet(rs);
				flowTaskDataSet.insertRowSet(rsRowSet);
			}
			
			if(flowTaskDataSet.getRowCount() == 0) continue;
			
			convertFlowTaskDataSet(PID + df.format(i + 1), FLOW_BH, FLOW_MC, flowNodeDataSet.getRowSet(i).getString("NODE_BH", ""), flowTaskDataSet);
			
			flowNodeDataSet.getRowSet(i).putString("id", PID + df.format(i + 1));
			flowNodeDataSet.getRowSet(i).putString("pid", PID);
			flowNodeDataSet.getRowSet(i).putString("FLOW_ID", FLOW_BH);
			flowNodeDataSet.getRowSet(i).putString("FLOW_MC", flowNodeDataSet.getRowSet(i).getString("FLOW_MC", "") + "[共有任务" + flowTaskDataSet.getRowCount() + "个]");
			
			flowNodeDataSet.getRowSet(i).setDataSet("FLOW_TASK_LIST", flowTaskDataSet);
			//计算节点下共有多少任务
			count += flowTaskDataSet.getRowCount();
		}
		return count;
	}
	
	/**
	 * 获取字典元数据
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public EFRowSet getDictRow(JStatement stmt, String tableName) {
		JConnection    conn = null;
		ResultSet        rs = null;
		EFDataSet    dictDS = EFDataSet.getInstance("SYS_DICTS");
		EFRowSet     dictRS = null;
		String       strSql = " select * from SYS_DICTS where DCT_ID = '" + tableName + "'";
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();
			rs = stmt.executeQuery(strSql);
			dictDS = DataSetUtils.resultSet2DataSet(rs, dictDS);
			if(dictDS != null && dictDS.getRowCount() > 0) {
				dictRS = dictDS.getRowSet(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dictRS;
	}
	
	/**
	 * 
	 * @param resultSet ResultSet
	 * @param efDataSet EFDataSet
	 * @return EFDataSet
	 * @throws Exception
	 */
	public EFRowSet resultSet2RowSet(ResultSet resultSet) throws Exception {
		EFRowSet rowSet = EFRowSet.getInstance();
		// 取出ResultSet的MetaData
		ResultSetMetaData rmd = resultSet.getMetaData();
		// 处理每一列数据
		int colCount = rmd.getColumnCount();
		String colName = null;
		Object value = null;
		for (int i = 1; i <= colCount; i++) {
			colName = rmd.getColumnLabel(i);
			value = resultSet.getObject(colName);
			// 如果
			if (rmd.getColumnType(i) == java.sql.Types.DATE || 
				rmd.getColumnType(i) == java.sql.Types.TIME || 
				rmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
				
				value = resultSet.getObject(colName);
				if (value instanceof java.util.Date) {
					value = ((java.util.Date) value);
				}
			} else {// 没法弄了，
				value = resultSet.getObject(colName);
				if (value instanceof java.util.Date) {
					continue;
				}
			}
			// BLOB CLOB不传递
			if (rmd.getColumnType(i) == java.sql.Types.BLOB
					|| rmd.getColumnType(i) == java.sql.Types.CLOB) {
				value = null;
			}
			if (value != null)
				rowSet.putString(colName, value.toString());
		}
		return rowSet;
	}
	
	public void convertFlowTaskDataSet(String PID, String FLOW_BH, String FLOW_MC, String NODE_ID, EFDataSet dataset) {
		String                    FmtStr = "000";
		java.text.DecimalFormat       df = new java.text.DecimalFormat(FmtStr);
		
		for(int i = 0; i < dataset.getRowCount(); i++) {
			dataset.getRowSet(i).putString("id", PID + df.format(i + 1));
			dataset.getRowSet(i).putString("pid", PID);
		}
	}
	
	public String convertEFDataSet2Json(EFDataSet flowDataSet) {
		EFRowSet             flowRowSet = null;
		EFRowSet         flowNodeRowSet = null;
		EFRowSet         flowTaskRowSet = null;
		EFDataSet       flowNodeDataSet = null;
		EFDataSet       flowTaskDataSet = null;
		JSONObject              jsonObj = new JSONObject();
		StringBuilder                sb = new StringBuilder();
		StringBuilder             newSb = new StringBuilder();
		StringBuilder         contentSb = new StringBuilder();
		
		sb.append("{Rows: [");
		
		//循环流程信息，形成JSON
		for(int i = 0; i < flowDataSet.getRowCount(); i++) {

			flowRowSet = flowDataSet.getRowSet(i);
			jsonObj = new JSONObject();
			jsonObj.putAll(flowRowSet);
			contentSb.append(jsonObj.toString() + ",");
			//循环流程节点信息
			flowNodeDataSet = flowRowSet.getDataSet("FLOW_NODE_LIST");
			for(int j = 0; j < flowNodeDataSet.getRowCount(); j++) {
				flowNodeRowSet = flowNodeDataSet.getRowSet(j);
				
				//循环流程代办任务信息
				flowTaskDataSet = flowNodeRowSet.getDataSet("FLOW_TASK_LIST");
				jsonObj.putAll(flowNodeRowSet);
				contentSb.append(jsonObj.toString() + ",");
				for(int k = 0; k < flowTaskDataSet.getRowCount(); k++) {
					flowTaskRowSet = flowTaskDataSet.getRowSet(k);
					jsonObj = new JSONObject();
					jsonObj.putAll(flowTaskRowSet);
					contentSb.append(jsonObj.toString() + ",");
				}
			}
		}
		if(contentSb.length() > 0) {
			sb.append(contentSb.substring(0, contentSb.length() - 1));
		}
		newSb.append(sb.toString());
		newSb.append("]}");
		System.out.print(newSb.toString());
		return newSb.toString();
	}
}
